﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Xml;
using MacomberMapSystem.Common.Serialization;
using System.Collections.Specialized;
using System.ComponentModel;
using MacomberMapSystem.Common.CIM;
using MacomberMapSystem.Common.Database;
using MacomberMapSystem.Common.User_Interfaces.One_Lines;
using System.Drawing;
using System.Threading;
using System.Windows.Forms;
using MacomberMapSystem.Common.CIM.Direct;
using MacomberMapSystem.Common.Internals;
using System.IO;
using System.Data.SQLite;
using System.Data.OleDb;
using Excel = Microsoft.Office.Interop.Excel;
using System.Reflection;
using System.Data.Common;
using System.Data;
using MacomberMapSystem.Common.Integration;

namespace MacomberMapSystem.Common.Processing
{
    /// <summary>
    /// This class handles the processing of breaker-to-breaker definitions and validations, creating contingency definitions, one-lines, and outage scheduler tracing
    /// </summary>
    public class CIM_BreakerToBreaker : CIM_Processor
    {
        #region Variable declarations
        /// <summary>
        /// The collection of seed element types to be processed for contingencies
        /// </summary>
        [Category("Configuration"), Description("The collection of seed element types to be processed for contingencies")]
        public StringCollection ContinegncySeedTypes
        {
            get { return _ContinegncySeedTypes; }
            set { _ContinegncySeedTypes = value; }
        }
        private StringCollection _ContinegncySeedTypes = new StringCollection();


        private StringCollection _OutageSchedulerSeedTypes = new StringCollection();
        /// <summary>
        /// The collection of seed element types to be processed for OS
        /// </summary>
        [Category("Configuration"), Description("The collection of seed element types to be processed for OS")]
        public StringCollection OutageSchedulerSeedTypes
        {
            get { return _OutageSchedulerSeedTypes; }
            set { _OutageSchedulerSeedTypes = value; }
        }

        private StringCollection _ContingencyTypesToNotOutput = new StringCollection(), _OutageSchedulerTypesToNotOutput = new StringCollection();
        /// <summary>
        /// The collection of types that shouldn't be outputted for contingencies
        /// </summary>
        [Category("Configuration"), Description("The collection of types that shouldn't be outputted for contingencies")]
        public StringCollection ContingencyTypesToNotOutput
        {
            get { return _ContingencyTypesToNotOutput; }
            set { _ContingencyTypesToNotOutput = value; }
        }

        /// <summary>
        /// The collection of types that shouldn't be outputted for OS
        /// </summary>
        [Category("Configuration"), Description("The collection of types that shouldn't be outputted for OS")]
        public StringCollection OutageSchedulerTypesToNotOutput
        {
            get { return _OutageSchedulerTypesToNotOutput; }
            set { _OutageSchedulerTypesToNotOutput = value; }
        }

        private XmlDocument _IncrementalUpdates = new XmlDocument();

        /// <summary>
        /// The incremental updates to be applied to our model.
        /// </summary>
        [Category("Configuration"), Description("The incremental updates to be applied to our model.")]
        public XmlDocument IncrementalUpdates
        {
            get { return _IncrementalUpdates; }
            set { _IncrementalUpdates = value; }
        }



        private bool _ExportOS;
        /// <summary>
        /// Whether the module should export OS tracing
        /// </summary>
        [Category("Configuration"), Description("Whether the module should export OS tracing")]
        public bool ExportOS
        {
            get { return _ExportOS; }
            set { _ExportOS = value; }
        }

        private bool _ExportContingency;
        /// <summary>
        /// Whether the module should export contingency tracing
        /// </summary>
        [Category("Configuration"), Description("Whether the module should export contingency tracing")]
        public bool ExportContingency
        {
            get { return _ExportContingency; }
            set { _ExportContingency = value; }
        }

        private bool _PlusOne;

        /// <summary>
        /// Whether the application should move to one element on the other side of the breaker
        /// </summary>
        [Category("Configuration"), Description("Whether the application should move to one element on the other side of the breaker")]
        public bool PlusOne
        {
            get { return _PlusOne; }
            set { _PlusOne = value; }
        }

        private bool _StopNormalOpen = true;
        /// <summary>
        /// Whether the application should stop parsing on a normally-opened switch
        /// </summary>
        [Category("Configuration"), Description("Whether the application should stop parsing on a normally-opened switch")]
        public bool StopNormalOpen
        {
            get { return _StopNormalOpen; }
            set { _StopNormalOpen = value; }
        }

        /// <summary>
        /// The type to search through (default is cim:ConnectivityNode)
        /// </summary>
        [Category("Configuration"), Description("The type to search through (default is cim:ConnectivityNode)")]
        public string TypeToSearch
        {
            get { return _TypeToSearch; }
            set { _TypeToSearch = value; }
        }

        private string _NormalOperationFlag, _TypeToSearch = "cim:ConnectivityNode";
        /// <summary>
        /// The flag to determine whether an element acts normally (e.g., breaker presume relay action, switch presume no relay action)
        /// </summary>
        [Category("Configuration"), Description("The flag to determine whether an element acts normally (e.g., breaker presume relay action, switch presume no relay action)")]
        public string NormalOperationFlag
        {
            get { return _NormalOperationFlag; }
            set { _NormalOperationFlag = value; }
        }
        #endregion


        /// <summary>
        /// Initialize a new breaker-to-breaker path
        /// </summary>
        /// <param name="xElem"></param>
        /// <param name="NamedParameters"></param>
        /// <param name="Repository"></param>
        public CIM_BreakerToBreaker(XmlElement xElem, Dictionary<String, Object> NamedParameters, MM_Repository Repository)
            : base(xElem, NamedParameters, Repository)
        {
            //Set up our default contingency configuration
            ContinegncySeedTypes.Add("cim:ACLineSegment");
            ContinegncySeedTypes.Add("cim:TransformerWinding");
            ContinegncySeedTypes.Add("cim:SynchronousMachine");
            ContingencyTypesToNotOutput.Add("cim:CustomerLoad");
            ContingencyTypesToNotOutput.Add("cim:Disconnector,etx:PowerSystemResource.contingencyComponent=true");
            ContingencyTypesToNotOutput.Add("cim:NonConformLoad");
            ContingencyTypesToNotOutput.Add("cim:ConnectivityNode");
            ContingencyTypesToNotOutput.Add("etx:PricingVector");
            ContingencyTypesToNotOutput.Add("etx:EndCap");
            ContingencyTypesToNotOutput.Add("cim:PowerTransformer");
            ContingencyTypesToNotOutput.Add("cim:BusbarSection");
            ContingencyTypesToNotOutput.Add("cim:GroundDisconnector");
            ContingencyTypesToNotOutput.Add("cim:StationSupply");
            ContingencyTypesToNotOutput.Add("cim:StaticVarCompensator");
            ContingencyTypesToNotOutput.Add("cim:ShuntCompensator");


            //Set up our default OS tracer configuration
            OutageSchedulerSeedTypes.Add("cim:ACLineSegment");
            OutageSchedulerSeedTypes.Add("cim:TransformerWinding");
            OutageSchedulerSeedTypes.Add("cim:SynchronousMachine");
            OutageSchedulerTypesToNotOutput.Add("cim:ConnectivityNode");
            OutageSchedulerTypesToNotOutput.Add("etx:PricingVector");
            OutageSchedulerTypesToNotOutput.Add("etx:EndCap");
            OutageSchedulerTypesToNotOutput.Add("cim:TransformerWinding");
            OutageSchedulerTypesToNotOutput.Add("cim:BusbarSection");

            //Set up our remaning characteristics
            ExportContingency = true;
            ExportOS = true;
            NormalOperationFlag = "etx:PowerSystemResource.contingencyComponent";
            PlusOne = false;
            TypeToSearch = "cim:ConnectivityNode";
        }

        /// <summary>
        /// Report the name of our process
        /// </summary>
        public override string ProcessName
        {
            get { return "Breaker-to-breaker tracing"; }
        }


        /// <summary>
        /// Initiate our process
        /// </summary>
        /// <param name="DbConn"></param>
        public override enumProcessState InitiateProcess(MM_Database_Connector DbConn)
        {
            //Get our model and collection of traces
            MM_Database_Model Model = NamedParameters["Model"] as MM_Database_Model;
            MM_Indexed_Dictionary<String, CIM_Trace> Traces = Model.Traces;
            Dictionary<CIM_Element, CIM_Trace.TraceState> NodeSeeds = new Dictionary<CIM_Element, CIM_Trace.TraceState>();
            Dictionary<CIM_RdfID, CIM_Element> CtgSeedElements = new Dictionary<CIM_RdfID, CIM_Element>();
            Dictionary<CIM_RdfID, CIM_Element> OSSeedElements = new Dictionary<CIM_RdfID, CIM_Element>();

            //Apply our incremental file. If we have an entry file hard-coded, pull it in for testing purposes.
            if (File.Exists(Path.Combine(Model.ModelFullPath, "ContingencyGroupingEntries.xml")))
                IncrementalUpdates.Load(Path.Combine(Model.ModelFullPath, "ContingencyGroupingEntries.xml"));

            if (IncrementalUpdates.DocumentElement != null)
            {
                Model.ModelChanges.Clear();
                Model.LoadIncrementalFile(IncrementalUpdates, true);
            }
            //Build our list of contingency groups


            Dictionary<CIM_Element, CIM_Contingency_Grouping> CtgGroups = new Dictionary<CIM_Element, CIM_Contingency_Grouping>();
            foreach (CIM_Element CtgGroup in Model.ElementsOfType("etx:ContingencyGroup"))
                CtgGroups.Add(CtgGroup, new CIM_Contingency_Grouping(CtgGroup, CtgGroups));
            foreach (CIM_Element CtgGroup in Model.ElementsOfType("etx:AggrContingencyGroup"))
                CtgGroups.Add(CtgGroup, new CIM_Contingency_Grouping(CtgGroup, CtgGroups));

            //Write out our contingency groups as a supplimentary file
            using (StreamWriter sW = new StreamWriter(Path.Combine(Model.ModelFullPath, Model.Name + "-ContingencyGroupingEntries.xml"), false, new UTF8Encoding(false)))
            {
                sW.WriteLine("<?xml version=\"1.0\"?>");
                sW.WriteLine("<rdf:RDF xmlns:rdf=\"http://www.w3.org/1999/02/22-rdf-syntax-ns#\" xmlns:cim=\"http://iec.ch/TC57/2006/CIM-schema-cim10#\" xmlns:spc=\"http://www.siemens-ptd/SHIMM1.0#\" xmlns:etx=\"http://www.ercot.com/CIM11R0/2008/2.0/extension#\">");
                foreach (String ElemToExport in "etx:ContingencyGroup,etx:AggrContingencyGroup,etx:ContSpecialElements,etx:ContingencyGroupMember,etx:GeographicObjects".Split(','))
                    foreach (CIM_Element Elem in Model.ElementsOfType(ElemToExport))
                        sW.WriteLine(Elem.XML);
                sW.WriteLine("</rdf:RDF>");
            }

            //Write out our Contingency definition file
            using (StreamWriter sW = new StreamWriter(Path.Combine(Model.ModelFullPath, Model.Name + "-ContingencyDefinitions.txt"), false, new UTF8Encoding(false)))
                foreach (CIM_Contingency_Grouping cGrp in CtgGroups.Values)
                    sW.WriteLine(cGrp.ToString());


            //Build our list of seed elements
            foreach (String str in this.ContinegncySeedTypes)
                foreach (CIM_Element Elem in Model.ElementsOfType(str))
                    if ((Elem.TypeName != "cim:TransformerWinding" && (String.IsNullOrEmpty(NormalOperationFlag) || String.IsNullOrEmpty(Elem[NormalOperationFlag]) || XmlConvert.ToBoolean(Elem[NormalOperationFlag]))) ||
                       (Elem.TypeName == "cim:TransformerWinding" && (String.IsNullOrEmpty(NormalOperationFlag) || String.IsNullOrEmpty(Elem.ParseSingle("cim:PowerTransformer", null)[NormalOperationFlag]) || XmlConvert.ToBoolean(Elem.ParseSingle("cim:PowerTransformer", null)[NormalOperationFlag]))))
                        CtgSeedElements.Add(Elem.rdfID, Elem);

            foreach (String str in this.OutageSchedulerSeedTypes)
                foreach (CIM_Element Elem in Model.ElementsOfType(str))
                    OSSeedElements.Add(Elem.rdfID, Elem);


            //Build our collection of nodes
            foreach (CIM_Element Node in Model.ElementsOfType("cim:ConnectivityNode"))
                NodeSeeds.Add(Node, new CIM_Trace.TraceState());
            Total = NodeSeeds.Count;


            //Clear our allocated contingencies for our tracing
            int ID = 0;
            Traces.Clear();
            foreach (CIM_Trace.TraceState TraceState in NodeSeeds.Values)
                TraceState.AllocatedCtg = null;


            //Ensure our prior model is opened, so we have access to those traces
            if (Model.PriorModel != null)
                if (!Model.PriorModel.EnsureOpened())
                    return enumProcessState.FailedWithErrors;

            //Make sure our reserved names are copied over
            Model.ReservedTraceNames.Clear();
            Model.ReservedTraceNames.AddRange(Model.PriorModel.ReservedTraceNames);
            this.Total = NodeSeeds.Count + Model.ElementsOfType("cim:Contingency").Length;

            //Build out our single-circuit contingencies
            foreach (KeyValuePair<CIM_Element, CIM_Trace.TraceState> kvp in NodeSeeds)
            {
                this.Current++;
                if (kvp.Value.AllocatedCtg == null)
                {
                    CIM_Trace NewTrace = new CIM_Trace(NodeSeeds, "N_" + Traces.Count.ToString(), Model, kvp.Key, PlusOne, StopNormalOpen, NormalOperationFlag, ID++);

                    //Determine the groups that to which the contingency belongs.                                            
                    NewTrace.DetermineGroups(CtgSeedElements, CtgGroups);


                    //Determine our trace's name and groups, and whether any RAS/SPSs should be attached                    
                    NewTrace.GenerateNameAndDescription(CtgSeedElements, ProcessLog, Deltas);

                    //Connect our trace to any RAS/SPSs.                                        
                    NewTrace.WireTrace();
                    if (Traces.ContainsKey(NewTrace.Name))
                        NewTrace.Name = "?_" + Traces.Count.ToString();
                    Traces.Add(ID, NewTrace.Name, NewTrace);
                }
            }

            //Build out the double-circuit continencies
            foreach (CIM_Element ExistingCtg in Model.ElementsOfType("cim:Contingency"))
            {
                this.Current++;
                if (ExistingCtg.Name.StartsWith("D"))
                {
                    CIM_Trace NewTrace = new CIM_Trace(NodeSeeds, ExistingCtg.Name, ContinegncySeedTypes, ExistingCtg, ID++);
                    NewTrace.Description = ExistingCtg.Attributes["cim:IdentifiedObject.description"];
                    NewTrace.WireTrace();
                    NewTrace.DetermineGroups(CtgSeedElements, CtgGroups);
                    NewTrace.ID = ID++;
                    Traces.Add(NewTrace.ID, NewTrace.Name, NewTrace);
                }
            }

            //Write out our contingecy group information
            using (StreamWriter sW = new StreamWriter(Path.Combine(Model.ModelFullPath, Model.Name + "-ContingencyGroupMembership.txt"), false, new UTF8Encoding(false)))
                foreach (CIM_Trace Trace in Traces.Values)
                {
                    sW.Write("{0} ({1})", Trace.Name, Trace.Description);
                    for (int a = 0; a < Trace.Groups.Length; a++)
                        sW.Write((a == 0 ? " - " : ", ") + Trace.Groups[a].Name);
                    sW.WriteLine();
                }

            NodeSeeds.Clear();

            //If we have a b2b test file, compare it.
            if (File.Exists(Path.Combine(Model.ModelFullPath, Model.Name + "-BreakerToBreakers.old.xml")))
                using (XmlTextWriter xW = new XmlTextWriter(Path.Combine(Model.ModelFullPath, Model.Name + "-BreakerToBreakerComparison.xml"), new UTF8Encoding(false)))
                {
                    XmlDocument xDoc = new XmlDocument();
                    xDoc.Load(Path.Combine(Model.ModelFullPath, Model.Name + "-BreakerToBreakers.old.xml"));

                }


            //If requested, export our contingency information
            if (ExportContingency)
                using (XmlTextWriter xW = new XmlTextWriter(Path.Combine(Model.ModelFullPath, Model.Name + "-BreakerToBreakers.xml"), new UTF8Encoding(false)))
                {
                    xW.Formatting = Formatting.Indented;
                    xW.WriteStartDocument();
                    xW.WriteStartElement("BreakerToBreakers");
                    foreach (CIM_Trace Trace in Traces.Values)
                        Trace.WriteXml(xW);
                    foreach (String ReservedUnit in Model.ReservedTraceNames)
                    {
                        xW.WriteStartElement("ReservedTraceName");
                        xW.WriteAttributeString("Value", ReservedUnit);
                        xW.WriteEndElement();
                    }


                    xW.WriteEndElement();
                }





            //If requested, export our OS information
            if (ExportOS)
                using (XmlTextWriter xW = new XmlTextWriter(Path.Combine(Model.ModelFullPath, "OS-Tracer-" + DateTime.Now.ToString("yyyy-MM-dd") + "-" + Model.Name + ".xml"), new UTF8Encoding(false)))
                {
                    xW.Formatting = Formatting.Indented;
                    xW.WriteStartDocument();
                    xW.WriteStartElement("ROWSET");
                    foreach (CIM_Trace Trace in Traces.Values)
                        if (!Trace.Name.StartsWith("D"))
                            foreach (CIM_Element TraceElem in Trace.Elements)
                                if (_OutageSchedulerSeedTypes.Contains(TraceElem.TypeName))
                                {
                                    Trace.WriteOSRelatedEquipment(xW, TraceElem, OutageSchedulerTypesToNotOutput);
                                    break;
                                }

                    xW.WriteEndElement();
                    xW.WriteEndDocument();
                    xW.Close();

                    //Also, write out our flag file
                    using (StreamWriter sW = new StreamWriter(Path.Combine(TargetFolder, "OS-Tracer-" + DateTime.Now.ToString("yyyy-MM-dd") + "-" + Model.Name + ".flg"), false, Encoding.UTF8))
                        sW.WriteLine("Completed OS tracer export on " + DateTime.Now.ToString() + ", " + (DateTime.Now - StartTime) + ", run by " + Environment.UserDomainName + "\\" + Environment.UserName);
                }

            //If requested, perform the work needed for contingencies
            if (ExportContingency)
            {
                //WriteExcelSpreadsheet();


            }
            return enumProcessState.Completed;
        }


        #region Excel exports
        /// <summary>
        /// Write out our Excel spreadsheet
        /// </summary>
        public static void WriteExcelSpreadsheet(List<String> LogLines, MM_Database_Model Model, MM_Repository Repository)
        {
            WriteExcelRawSpreadsheet(LogLines, Model, Repository);
            EnhanceExcelSpreadsheet(LogLines, Model, Repository);
        }

        /// <summary>
        /// Write out our raw spreadsheet
        /// </summary>
        /// <param name="LogLines"></param>
        /// <param name="Model"></param>
        /// <param name="Repository"></param>
        private static void WriteExcelRawSpreadsheet(List<String> LogLines, MM_Database_Model Model, MM_Repository Repository)
        {
            Dictionary<String, Object> OutValues = new Dictionary<string, object>();
            using (OleDbConnection oleExcel = new OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + Path.Combine(Model.ModelFullPath, Model.Name + "-Report_Updated.xlsx") + ";Extended Properties=\"Excel 12.0 Xml;HDR=YES\""))
            {
                oleExcel.Open();
                StringBuilder xCols = new StringBuilder("CREATE TABLE [Full List] ([Contingency ID] String, [Contingency Description] String, [Radial] Integer, [Substations] String, [Substation Long Names] String, [Counties] String, [Element type] String, [Element name] String, [Element TEID] string, [PSSE ID] String, [From bus] String, [To bus] String, [Owner] String, [Operator] String");
                foreach (String cGrp in CIM_Trace.FullGroupNames.Keys)
                    xCols.Append(", [" + cGrp + "] String");
                xCols.Append(")");

                using (OleDbCommand oCmd = new OleDbCommand(xCols.ToString(), oleExcel))
                    oCmd.ExecuteNonQuery();
                using (OleDbCommand oCmd = new OleDbCommand("CREATE TABLE [DeltaLog] ([Notes] String)", oleExcel))
                    oCmd.ExecuteNonQuery();

                //Now, export our log lines
                foreach (String str in LogLines)
                    using (OleDbCommand oCmd = new OleDbCommand("INSERT INTO [DeltaLog] ([Notes]) VALUES (:Note)", oleExcel))
                    {
                        oCmd.Prepare();
                        oCmd.Parameters.AddWithValue("Note", str);
                        oCmd.ExecuteNonQuery();
                    }


                //Now, export every contingency list
                SortedDictionary<String, CIM_Trace> SortedDefinitions = new SortedDictionary<string, CIM_Trace>();
                foreach (CIM_Trace Trace in Model.Traces.Values)
                    // if (!Trace.Name.StartsWith("N_"))
                    SortedDefinitions.Add(Trace.Name, Trace);

                int CurrentCtg = 0;
                foreach (CIM_Trace cDef in SortedDefinitions.Values)
                {
                    Console.WriteLine("Writing contingency definition {0}: {1:#,##0} / {2:#,##0} ({3:0.0%})", cDef.Name, CurrentCtg++, SortedDefinitions.Count, (float)CurrentCtg / (float)SortedDefinitions.Count);

                    //Write our header list
                    OutValues.Clear();
                    OutValues.Add("Contingency ID", cDef.Name);
                    OutValues.Add("Contingency Description", cDef.Description);
                    OutValues.Add("Radial", cDef.Radial);
                    foreach (String cGrp in cDef.GroupNames)
                        OutValues.Add(cGrp, "Yes");
                    InsertTableRow(oleExcel, "Full List", OutValues);

                    String CtgAttribute = "etx:PowerSystemResource.contingencyComponent";
                    Dictionary<String, String> IgnoreOnTrace = new Dictionary<string, string>();
                    IgnoreOnTrace.Add("cim:CustomerLoad", null);
                    IgnoreOnTrace.Add("cim:Disconnector", "etx:PowerSystemResource.contingencyComponent=true");
                    IgnoreOnTrace.Add("cim:NonConformLoad", null);
                    IgnoreOnTrace.Add("cim:ConnectivityNode", null);
                    IgnoreOnTrace.Add("etx:PricingVector", null);
                    IgnoreOnTrace.Add("etx:EndCap", null);
                    IgnoreOnTrace.Add("cim:PowerTransformer", null);
                    IgnoreOnTrace.Add("cim:BusbarSection", null);
                    IgnoreOnTrace.Add("cim:GroundDisconnector", null);
                    IgnoreOnTrace.Add("cim:StationSupply", null);
                    IgnoreOnTrace.Add("cim:StaticVarCompensator", null);
                    IgnoreOnTrace.Add("cim:ShuntCompensator", null);

                    //Build our list of exportable elements
                    String ParamCheck = null;
                    bool IncludeSeedsInDoubles = true;
                    List<CIM_Element> ExportableElements = new List<CIM_Element>();
                    List<CIM_Element> FlaggedSeeds = new List<CIM_Element>();

                    //If we're a double-circuit, build our list of flagged seeds.
                    CIM_Element BaseCtg;
                    if ((BaseCtg = Model.FromRdfID(cDef.rdfID)) != null)
                        FlaggedSeeds.AddRange(BaseCtg.Elements);




                    foreach (CIM_Element Elem in cDef.Elements)
                        if (IgnoreOnTrace.TryGetValue(Elem.TypeName, out ParamCheck) && ParamCheck == null)
                        { }

                        //If we have a parameter, check the exclusion criteria. If set to exclude, do nothing, otherwise export implicitly
                        else if (ParamCheck != null && Elem[ParamCheck.Split('=')[0]].Equals(ParamCheck.Split('=')[1], StringComparison.CurrentCultureIgnoreCase))
                        { }
                        else if (ParamCheck != null && !Elem[ParamCheck.Split('=')[0]].Equals(ParamCheck.Split('=')[1], StringComparison.CurrentCultureIgnoreCase))
                            ExportableElements.Add(Elem);

                        //Otherwise, if we have a transformer winding look at power transformers, or look at the exportable element flag.                                                        
                        else if (Elem.Start == -1)
                            Console.WriteLine("**** ERROR! Element " + Elem.rdfID.ToString() + " has no XML! ****");

                        else if (IncludeSeedsInDoubles && FlaggedSeeds.Contains(Elem))
                            ExportableElements.Add(Elem);

                        else if ((Elem.TypeName != "cim:TransformerWinding" && (String.IsNullOrEmpty(CtgAttribute) || String.IsNullOrEmpty(Elem[CtgAttribute]) || XmlConvert.ToBoolean(Elem[CtgAttribute]))) ||
                            (Elem.TypeName == "cim:TransformerWinding" && (String.IsNullOrEmpty(CtgAttribute) || String.IsNullOrEmpty(Elem.ParseSingle("cim:PowerTransformer", null)[CtgAttribute]) || XmlConvert.ToBoolean(Elem.ParseSingle("cim:PowerTransformer", null)[CtgAttribute]))))
                            ExportableElements.Add(Elem);





                    //Write our collection of exportable elements                     
                    foreach (CIM_Element Elem in ExportableElements)
                    {
                        OutValues.Clear();
                        if (Elem.TypeName == "cim:ACLineSegment" || Elem.TypeName == "cim:SeriesCompensator")
                        {
                            OutValues.Add("Substations", Elem.ACSegmentStations[0].Name + " to " + Elem.ACSegmentStations[1].Name);
                            MM_Line FoundLine;
                            String SubName;
                            String County;

                            if (Repository.Db.Lines.TryGetValue(Elem.rdfID, out FoundLine))
                            {
                                SubName = FoundLine.FromSubstation.LongName + " to " + FoundLine.ToSubstation.LongName;
                                County = FoundLine.FromSubstation.Boundary == FoundLine.ToSubstation.Boundary ? FoundLine.FromSubstation.Boundary.Name : FoundLine.FromSubstation.Boundary.Name + " to " + FoundLine.ToSubstation.Boundary.Name;
                            }
                            else
                            {
                                SubName = Elem.ACSegmentStations[0].Name + " to " + Elem.ACSegmentStations[1].Name;
                                County = "Unknown";
                            }

                            OutValues.Add("Substation Long Names", SubName);
                            OutValues.Add("Counties", County);
                            OutValues.Add("Element name", Elem.LineName);
                        }
                        else
                        {
                            OutValues.Add("Substations", Elem.Substation.Name);


                            MM_Substation FoundSub;
                            if (Repository.Db.Substations.TryGetValue(Elem.Substation.rdfID, out FoundSub))
                            {
                                OutValues.Add("Substation Long Names", Elem.Substation.LongName);
                                OutValues.Add("Counties", FoundSub.Boundary.Name);
                            }
                            else
                            {
                                OutValues.Add("Substation Long Names", Elem.Name);
                                OutValues.Add("Counties", "Unknown");
                            }
                            OutValues.Add("Element name", Elem.Name);
                        }
                        OutValues.Add("Element type", Elem.TypeName.Split(':')[1]);
                        OutValues.Add("Element TEID", Elem.TEID);
                        OutValues.Add("PSSE ID", Elem["etx:Equipment.psseid"]);
                        CIM_Element Owner = Elem.Owner;
                        if (Owner == null)
                            Owner = Elem.ParseSingle("cim:Line>etx:Ownership>cim:Company", Model);
                        if (Owner == null)
                            OutValues.Add("Owner", DBNull.Value);
                        else
                            OutValues.Add("Owner", Owner.Attributes["cim:IdentifiedObject.aliasName"]);


                        CIM_Element Operator = Elem.Operator;
                        if (Operator == null)
                            OutValues.Add("Operator", DBNull.Value);
                        else
                            OutValues.Add("Operator", Elem.Operator.Attributes["cim:IdentifiedObject.aliasName"]);


                        CIM_Element[] Nodes = Elem.Nodes;
                        OutValues.Add("From bus", Nodes[0]["etx:ConnectivityNode.PSSEBusNumber"]);

                        if (Nodes.Length > 1 && Nodes[0] != Nodes[1])
                            OutValues.Add("To bus", Nodes[1]["etx:ConnectivityNode.PSSEBusNumber"]);

                        InsertTableRow(oleExcel, "Full List", OutValues);
                    }
                }
            }

        }


        /// <summary>
        /// Enhance our Excel spreadsheet
        /// </summary>
        /// <param name="LogLines"></param>
        /// <param name="Model"></param>
        /// <param name="Repository"></param>
        private static void EnhanceExcelSpreadsheet(List<String> LogLines, MM_Database_Model Model, MM_Repository Repository)
        {
            //Now, open up our file in Excel, and perform our editing
            Excel.Application xlApp = new Excel.Application();
            xlApp.Visible = true;
            Excel.Workbook Book = xlApp.Workbooks.Open(Path.Combine(Model.ModelFullPath, Model.Name + "-Report_Updated.xlsx"), Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value);
            MM_Excel_Worksheet FullList = new MM_Excel_Worksheet(Book.Sheets["Full_List"] as Excel.Worksheet);
            FullList.Name = "Lookup_Table";
            FullList.Cells.Font.Name = "Calibri";
            FullList.Cells.Font.Size = 11;

            //Create our Lookup table
            MM_Excel_Worksheet LookupTable = new MM_Excel_Worksheet(Book.Sheets.Add(Missing.Value, Missing.Value, Missing.Value, Missing.Value) as Excel.Worksheet);
            LookupTable.Name = "Standard_Contingency_List";
            LookupTable.Cells.Font.Name = "Calibri";
            LookupTable.Cells.Font.Size = 11;
            LookupTable[1, 1].set_Value(Missing.Value, "Name");
            LookupTable[1, 2].set_Value(Missing.Value, "Description");
            int OutRow = 2;
            for (int a = 2; a <= FullList.BaseSheet.Cells.SpecialCells(Excel.XlCellType.xlCellTypeLastCell, Missing.Value).Row; a++)
                if (!String.IsNullOrEmpty(FullList[a, 1].get_Value(Missing.Value) as string))
                {
                    if (String.IsNullOrEmpty(FullList[a, 2].get_Value(Missing.Value) as string))
                        FullList[a, 2].set_Value(Missing.Value, "(unknown)");
                    LookupTable[OutRow, 1].set_Value(Missing.Value, FullList[a, 1].get_Value(Missing.Value));
                    LookupTable[OutRow, 2].set_Value(Missing.Value, FullList[a, 2].get_Value(Missing.Value));
                    LookupTable.Hyperlinks.Add(LookupTable.Cells[OutRow, 2], "", "='" + FullList.Name + "'!A" + a.ToString(), "", FullList[a, 2].get_Value(Missing.Value));
                    FullList.Hyperlinks.Add(FullList[a, 1], "", "='" + LookupTable.Name + "'!A" + OutRow.ToString(), "", FullList[a, 1].get_Value(Missing.Value));
                    OutRow++;
                    if (OutRow % 5 == 0)
                    {
                        Console.WriteLine("Writing row {0:#,##0}/{1:#,##0}", a, FullList.BaseSheet.Cells.SpecialCells(Excel.XlCellType.xlCellTypeLastCell, Missing.Value).Row);
                        Application.DoEvents();
                    }
                }

            //Write out our introduction
            MM_Excel_Worksheet IntroductionTable = new MM_Excel_Worksheet(Book.Sheets.Add(Book.Sheets[1], Missing.Value, Missing.Value, Missing.Value) as Excel.Worksheet);
            IntroductionTable.Name = "Introduction";
            int ColWidth = 8;
            IntroductionTable.Cells.Font.Name = "Calibri";
            IntroductionTable.Cells.Font.Size = 11;

            //Add in our ERCOT image

            IntroductionTable.BaseSheet.Shapes.AddPicture(Path.Combine(Application.StartupPath, "ERCOT_Logo.bmp"), Microsoft.Office.Core.MsoTriState.msoFalse, Microsoft.Office.Core.MsoTriState.msoCTrue, 0, 0, 326, 132);

            IntroductionTable[9, 1].set_Value(Missing.Value, "Model:");
            IntroductionTable[9, 2].set_Value(Missing.Value, Path.GetFileNameWithoutExtension(Model.ModelFullPath));
            IntroductionTable[9, 2, 9, ColWidth].Merge(Missing.Value);
            IntroductionTable[10, 1].set_Value(Missing.Value, "Run on:");
            IntroductionTable[10, 2].set_Value(Missing.Value, DateTime.Now.ToString());
            IntroductionTable[10, 2].HorizontalAlignment = Excel.Constants.xlLeft;
            IntroductionTable[10, 2, 10, ColWidth].Merge(Missing.Value);

            IntroductionTable[11, 1].set_Value(Missing.Value, "Contingency Definition Guide");
            IntroductionTable[11, 1, 11, ColWidth].Merge(Missing.Value);
            IntroductionTable[11, 1].WrapText = true;
            IntroductionTable[11, 1].Font.Size = 22;
            IntroductionTable[11].RowHeight = 28;
            IntroductionTable[11, 1].HorizontalAlignment = Excel.Constants.xlCenter;


            IntroductionTable[13, 1].set_Value(Missing.Value, "In nodal, contingency definitions are automatically generated against the network model, around transmission lines, units, and transformers, as well as for double-circuits. The definitions are built by starting at each seed element, and following the topology connections until finding breakers with relay action or open switches.");
            IntroductionTable[13, 1, 17, ColWidth].Merge(Missing.Value);
            IntroductionTable[13, 1].WrapText = true;

            IntroductionTable[19, 1].set_Value(Missing.Value, "Double-Circuits:");
            IntroductionTable[19, 1].Font.Bold = true;
            IntroductionTable[19, 1, 19, ColWidth].Merge(Missing.Value);
            IntroductionTable[19, 1].WrapText = true;
            IntroductionTable[20, 1].set_Value(Missing.Value, "Names starting with 'D'");
            IntroductionTable[20, 4].set_Value(Missing.Value, "The network model contains the contingency name, and references to the latest seed elements. ");
            IntroductionTable[20, 4, 21, ColWidth].Merge(Missing.Value);
            IntroductionTable[20, 4].WrapText = true;



            IntroductionTable[23, 1].set_Value(Missing.Value, "Single-circuit transmission lines:");
            IntroductionTable[23, 1].Font.Bold = true;
            IntroductionTable[24, 1].set_Value(Missing.Value, "[Letter 'S']");
            IntroductionTable[25, 1].set_Value(Missing.Value, "[First three letters of the substation with highest total normal line MVA ratings]");
            IntroductionTable[26, 1].set_Value(Missing.Value, "[First three letters of the substation with lowest total normal line MVA ratings]");
            IntroductionTable[27, 1].set_Value(Missing.Value, "[The last number in the KV (e.g., 5 for 345 KV)]");

            IntroductionTable[29, 1].set_Value(Missing.Value, "Transformers:");
            IntroductionTable[29, 1].Font.Bold = true;
            IntroductionTable[30, 1].set_Value(Missing.Value, "[Letter 'X']");
            IntroductionTable[31, 1].set_Value(Missing.Value, "[First three letters of the substation]");
            IntroductionTable[32, 1].set_Value(Missing.Value, "[The last number in the high-side KV (e.g., 5 for 345 KV)]");
            IntroductionTable[33, 1].set_Value(Missing.Value, "[The last number in the low-side KV (e.g., 9 for 69 KV)]");


            IntroductionTable[35, 1].set_Value(Missing.Value, "Units:");
            IntroductionTable[35, 1].Font.Bold = true;
            IntroductionTable[36, 1].set_Value(Missing.Value, "[Letter 'U']");
            IntroductionTable[37, 1].set_Value(Missing.Value, "[First three letters of the substation]");
            IntroductionTable[38, 1].set_Value(Missing.Value, "[First three letters of the unit]");
            IntroductionTable[39, 1].set_Value(Missing.Value, "[Number of units in the definition]");

            IntroductionTable[41, 1].set_Value(Missing.Value, "Others:");
            IntroductionTable[41, 1].Font.Bold = true;
            IntroductionTable[42, 1].set_Value(Missing.Value, "Breaker-to-breaker definitions utilized in a one-line program by ERCOT staff. These names start with N_ and a number, and are not exported to or used by EMS, MMS or OS systems.");
            IntroductionTable[42, 1, 43, ColWidth].Merge(Missing.Value);
            IntroductionTable[42, 1].WrapText = true;

            IntroductionTable[47, 1].set_Value(Missing.Value, "Duplication handling:");
            IntroductionTable[47, 1].Font.Bold = true;
            IntroductionTable[48, 1].set_Value(Missing.Value, "In the event that a contingency name is duplicated, the last letter of the second substation (single-circuit line), or substation (transformers or units) is replaced by a number.");
            IntroductionTable[48, 1, 49, ColWidth].Merge(Missing.Value);
            IntroductionTable[48, 1].WrapText = true;
            Book.Save();

            xlApp.Quit();
        }

        /// <summary>
        /// Insert a row into a table
        /// </summary>
        /// <param name="dConn"></param>
        /// <param name="TableName"></param>
        /// <param name="OutValues"></param>
        private static void InsertTableRow(DbConnection dConn, String TableName, Dictionary<String, object> OutValues)
        {
            StringBuilder sBd = new StringBuilder();
            StringBuilder sBd2 = new StringBuilder();
            int CurNum = 2;
            foreach (string StrToExport in OutValues.Keys)
                if (sBd.Length == 0)
                {
                    sBd.Append("INSERT INTO [" + TableName + "] ([" + StrToExport + "]");
                    sBd2.Append(") VALUES (:1");
                }
                else
                {
                    sBd.Append(",[" + StrToExport + "]");
                    sBd2.Append(",:" + CurNum++);
                }

            //Build our command, and execute
            using (DbCommand dCmd = dConn.CreateCommand())
            {
                dCmd.CommandText = sBd.ToString() + sBd2.ToString() + ")";
                dCmd.Prepare();
                CurNum = 1;
                foreach (Object obj in OutValues.Values)
                    AddParameter(dCmd, ":" + CurNum++, obj);
                dCmd.ExecuteNonQuery();
            }
        }

        /// <summary>
        /// Add a parameter to a query
        /// </summary>
        /// <param name="sCmd"></param>
        /// <param name="ParamName"></param>
        /// <param name="ParamValue"></param>
        private static void AddParameter(DbCommand sCmd, String ParamName, Object ParamValue)
        {
            DbParameter dParam = sCmd.CreateParameter();
            dParam.ParameterName = ParamName;
            if (ParamValue == null)
                dParam.Value = DBNull.Value;
            else
                dParam.Value = ParamValue;
            sCmd.Parameters.Add(dParam);
        }
        #endregion

        /// <summary>
        /// Open a one-line of specified information
        /// </summary>
        /// <param name="obj"></param>
        [STAThread]
        private void OpenOneLine(Object obj)
        {
            Thread.CurrentThread.SetApartmentState(ApartmentState.STA);
            object[] inObj = (object[])obj;
            Application.Run(new frmOneLineEditor(inObj[0] as MM_Database_OneLine, inObj[1] as MM_Database_Model, inObj[2] as CIM_Trace, inObj[3] as MM_Repository));
        }

        /// <summary>
        /// Utilize TSL to execute an automatically-generated one-line
        /// </summary>
        /// <param name="BaseElement"></param>
        public static XmlDocument AutoGenerateOneLine(CIM_Element BaseElement)
        {
            Dictionary<CIM_RdfID, XmlElement> Elements = new Dictionary<CIM_RdfID, XmlElement>();

            //First, go throught all physical elements to create our oneline
            XmlDocument xDoc = new XmlDocument();
            xDoc.AppendChild(xDoc.CreateElement("One_Line"));
            xDoc.DocumentElement.Attributes.Append(xDoc.CreateAttribute("rdfID")).Value = BaseElement.rdfID.ToString();
            xDoc.DocumentElement.Attributes.Append(xDoc.CreateAttribute("BaseElement.Name")).Value = BaseElement.TypeName == "cim:Company" ? BaseElement["cim:IdentifiedObject.aliasName"] : BaseElement.Name;
            xDoc.DocumentElement.Attributes.Append(xDoc.CreateAttribute("BaseElement.LongName")).Value = BaseElement.LongName;
            XmlElement xElems = xDoc.DocumentElement.AppendChild(xDoc.CreateElement("Elements")) as XmlElement;
            XmlElement xNodes = xDoc.DocumentElement.AppendChild(xDoc.CreateElement("Nodes")) as XmlElement;

            //Now, go through and add in our elements, one by one
            foreach (CIM_Element Elem in BaseElement.Elements)
                if (Elem.ElemType != "Node" && Elem.ElemType != "PricingVector")
                    if (Array.IndexOf(Enum.GetNames(typeof(MM_OneLine_Element.enumElemTypes)), Elem.ElemType) != -1)
                        try
                        {
                            xElems.AppendChild(CreateElement(Elem, BaseElement, xDoc, Elements));
                        }
                        catch (Exception ex)
                        {
                            throw new InvalidOperationException("Error exporting " + Elem.ToString() + " for " + BaseElement.ToString(), ex);
                        }

            //Do the same for nodes, and link up with everything
            foreach (CIM_Element Elem in BaseElement.Elements)
                if (Elem.ElemType == "Node")
                    //try {
                    xNodes.AppendChild(CreateElement(Elem, BaseElement, xDoc, Elements));
            /* }
             catch (Exception ex)
             {
                 throw new InvalidOperationException("Error exporting " + Elem.ToString() + " for " + BaseElement.ToString(), ex);
             }*/


            return xDoc;
        }

        /// <summary>
        /// Create an XML element to represent a component
        /// </summary>
        /// <param name="Elem">The CIM element</param>
        /// <param name="xDoc">The Xml document</param>
        /// <param name="Elements">The collection of existing elements by rdf:ID</param>
        /// <param name="BaseElement"></param>
        /// <returns></returns>
        private static XmlNode CreateElement(CIM_Element Elem, CIM_Element BaseElement, XmlDocument xDoc, Dictionary<CIM_RdfID, XmlElement> Elements)
        {

            using (MM_OneLine_Element mElem = new MM_OneLine_Element(Elem, BaseElement, xDoc))
            {
                Elements.Add(Elem.rdfID, mElem.Definition);
                CIM_Element XF;
                if (Elem.ElemType == "Node")
                    foreach (CIM_Element ElemNode in Elem.Elements)
                        if (ElemNode.ElemType == "TransformerWinding" && (XF = ElemNode.ParseSingle("cim:PowerTransformer", null)) != null && Elements.ContainsKey(XF.rdfID))
                        {
                            XmlElement NodeElem = xDoc.CreateElement(XF.ElemType);
                            NodeElem.Attributes.Append(xDoc.CreateAttribute("rdfID")).Value = XF.rdfID.ToString();
                            NodeElem.Attributes.Append(xDoc.CreateAttribute("TEID")).Value = XF.TEID;
                            mElem.Definition.AppendChild(NodeElem);
                        }
                        else if (Elements.ContainsKey(ElemNode.rdfID))
                        {
                            XmlElement NodeElem = xDoc.CreateElement(ElemNode.ElemType);
                            NodeElem.Attributes.Append(xDoc.CreateAttribute("rdfID")).Value = ElemNode.rdfID.ToString();
                            NodeElem.Attributes.Append(xDoc.CreateAttribute("TEID")).Value = ElemNode.TEID;
                            mElem.Definition.AppendChild(NodeElem);
                        }
                return mElem.Definition;
            }
        }

        /// <summary>
        /// Determine whether our definitions are radial
        /// </summary>
        /// <param name="BreakerTally">Our breaker tally</param>
        /// <param name="Traces"></param>
        private void DefineRadials(Dictionary<CIM_Element, int> BreakerTally, List<CIM_Trace> Traces)
        {
            Dictionary<CIM_Element, bool> RadialBreakers = new Dictionary<CIM_Element, bool>();
            bool FoundRadial = true;
            int CurIncrement = 0;
            while (FoundRadial)
            {
                FoundRadial = false;
                foreach (CIM_Trace cDef in Traces)
                    if (cDef.Radial == -1 && cDef.Name[0] != 'D' && cDef.CheckRadial(RadialBreakers, NormalOperationFlag, CurIncrement, BreakerTally))
                        FoundRadial = true;
                CurIncrement++;
            }
        }
    }
}